IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Consultant]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   DROP TABLE [dbo].[Consultant]
GO

/*----------------------------------------------------------------------
'Created By : Preeti
'Created On : 10/18/2008
'SP Name    : dbo.SP_SetConsultant
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE TABLE [dbo].[Consultant] (
   ConsultantID   Varchar(30),
   [LastName]     Varchar(50),
   [FirstName]     Varchar(50),
   [Name]     Varchar(50),
   [Address]     Varchar(50),
   [City]     Varchar(50),
   [State]     Varchar(50),
   [ZipCode]     Varchar(50),
   [MobilePhone]     Varchar(50),
   [OfficePhone]     Varchar(50),
   [HomePhone]     Varchar(50),
   [EmailAddress]     Varchar(50),
   [EmergencyContactName]     Varchar(50),
   [EmergencyContactPhone]     Varchar(50),
   [Specialization]     Varchar(50),
   [Remarks]     Varchar(50),
   [Fees]     Float
)
GO

GRANT SELECT,INSERT,DELETE,UPDATE ON dbo.Consultant TO hms_usr
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.SP_GetConsultant') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.SP_GetConsultant
GO


/*----------------------------------------------------------------------
'Created By : Preeti
'Created On : 10/18/2008
'SP Name    : dbo.SP_GetConsultant
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE PROCEDURE dbo.SP_GetConsultant
@vchConsultantID VARCHAR(30)
AS
   SELECT
       ConsultantID,
       LastName,
       FirstName,
       [Name] AS FullName,
       Address,
       City,
       State,
       ZipCode,
       MobilePhone,
       OfficePhone,
       HomePhone,
       EmailAddress,
       EmergencyContactName,
       EmergencyContactPhone,
       Specialization,
       Remarks,
       Fees
   FROM Consultant
   WHERE ConsultantID=@vchConsultantID
GO

GRANT EXEC ON dbo.SP_GetConsultant TO hms_usr
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.SP_SetConsultant') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.SP_SetConsultant
GO


/*----------------------------------------------------------------------
'Created By : Preeti
'Created On : 10/18/2008
'SP Name    : dbo.SP_SetConsultant
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE PROCEDURE dbo.SP_SetConsultant
@vchConsultantID Varchar(30),
@vchLastName    Varchar(50),
@vchFirstName    Varchar(50),
@vchFullName    Varchar(50),
@vchAddress    Varchar(50),
@vchCity    Varchar(50),
@vchState    Varchar(50),
@vchZipCode    Varchar(50),
@vchMobilePhone    Varchar(50),
@vchOfficePhone    Varchar(50),
@vchHomePhone    Varchar(50),
@vchEmailAddress    Varchar(50),
@vchEmergencyContactName    Varchar(50),
@vchEmergencyContactPhone    Varchar(50),
@vchSpecialization    Varchar(50),
@vchRemarks    Varchar(50),
@fltFees    Float
AS
DECLARE  @iRetValue     integer
   IF NOT EXISTS(SELECT     ConsultantID
   FROM Consultant
   WHERE   (ConsultantID = @vchConsultantID)
   )

   BEGIN
   INSERT INTO Consultant(
       ConsultantID,
       LastName,
       FirstName,
       [Name],
       Address,
       City,
       State,
       ZipCode,
       MobilePhone,
       OfficePhone,
       HomePhone,
       EmailAddress,
       EmergencyContactName,
       EmergencyContactPhone,
       Specialization,
       Remarks,
       Fees
  )
   VALUES (
      @vchConsultantID,
       @vchLastName,
       @vchFirstName,
       @vchFullName,
       @vchAddress,
       @vchCity,
       @vchState,
       @vchZipCode,
       @vchMobilePhone,
       @vchOfficePhone,
       @vchHomePhone,
       @vchEmailAddress,
       @vchEmergencyContactName,
       @vchEmergencyContactPhone,
       @vchSpecialization,
       @vchRemarks,
       @fltFees
   )
   END
ELSE  IF EXISTS(SELECT     ConsultantID
   FROM Consultant
   WHERE   (ConsultantID = @vchConsultantID)
   )

   BEGIN
   UPDATE Consultant
   SET
       LastName = @vchLastName,
       FirstName = @vchFirstName,
       [Name] = @vchFullName,
       Address = @vchAddress,
       City = @vchCity,
       State = @vchState,
       ZipCode = @vchZipCode,
       MobilePhone = @vchMobilePhone,
       OfficePhone = @vchOfficePhone,
       HomePhone = @vchHomePhone,
       EmailAddress = @vchEmailAddress,
       EmergencyContactName = @vchEmergencyContactName,
       EmergencyContactPhone = @vchEmergencyContactPhone,
       Specialization = @vchSpecialization,
       Remarks = @vchRemarks,
       Fees = @fltFees
  WHERE (ConsultantID = @vchConsultantID)
   END

IF (@@ERROR <> 0)
   BEGIN
       declare @SPErrMsg varchar(800)
       SET @SPErrMsg = 'Error: Error in Stored procedure dbo.SP_SetConsultant for Loan# : ' + CAST (@vchConsultantID as varchar)
       RAISERROR (@SPErrMsg,16,1)
       Select @iRetValue = -1
   END
ELSE
   BEGIN
       Select @iRetValue = 0
   END
   
   RETURN @iRetValue
GO

GRANT EXEC ON dbo.SP_SetConsultant TO hms_usr
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.SP_DeleteConsultant') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.SP_DeleteConsultant
GO


/*----------------------------------------------------------------------
'Created By : Preeti
'Created On : 10/18/2008
'SP Name    : dbo.SP_DeleteConsultant
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE PROCEDURE dbo.SP_DeleteConsultant
@vchConsultantID VARCHAR(30)
AS
   DELETE FROM Consultant  WHERE   (ConsultantID = @vchConsultantID)

GRANT EXEC ON dbo.SP_DeleteConsultant TO hms_usr
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.SP_GetConsultantFees') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.SP_GetConsultantFees
GO


/*----------------------------------------------------------------------
'Created By : Swati
'Created On : 10/03/2008
'SP Name    : dbo.SP_GetConsultantFees
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE PROCEDURE [dbo].[SP_GetConsultantFees]
AS
	SELECT
       [Name],
	   Fees
   FROM Consultant

GO

GRANT EXEC ON dbo.SP_GetConsultantFees TO hms_usr
GO
